package EBM.DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import EBM.model.Project;

public class ProjectDAO {
	private List<Object> response;
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	private String sql;
	private int result;
	private Project project;

	public ProjectDAO(){
		response = null;
		conn = null;
		ps = null;
		rs = null;
		sql = null;
		result = 0;
	}
	
	public List<Object> findAll() 
	throws SQLException, ClassNotFoundException{

		sql="select * from project";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();		

		rs=ps.executeQuery();

		if(rs.next()){
			do{
				response.add(rs.getObject("proid"));
				response.add(rs.getObject("name"));
			}while(rs.next());
		}
		conn.close();

		return response;
	}

	public List<Object> findById(Object id) 
	throws SQLException, ClassNotFoundException{

		sql="select * from project where id=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();		

		ps.setObject(1, id);

		rs=ps.executeQuery();

		if(rs.next()){
			project = new Project();
			project.setId(rs.getObject("id").toString());
			project.setProid(rs.getObject("proid").toString());
			project.setName(rs.getObject("name").toString());
			project.setVersion(rs.getObject("version").toString());

			response.add(project);
		}
		conn.close();

		return response;
	}

	public List<Object> findByProid(Object proId) 
	throws SQLException, ClassNotFoundException{

		sql="select * from project where proid=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();		

		ps.setObject(1, proId);

		rs=ps.executeQuery();

		if(rs.next()){
			response.add(rs.getObject("id"));
		}
		conn.close();

		return response;
	}

	public List<Object> findByuserid(Object userid)
	throws SQLException, ClassNotFoundException{

		sql="select distinct project.id,project.proid,project.name from project,project_user,userinfo"+
		" where project.id=project_user.proid and project_user.usrid=userinfo.id and userinfo.usrid=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);
		ps.setObject(1, userid);

		response = new ArrayList<Object>();		

		rs=ps.executeQuery();

		if(rs.next()){
			do{
				project = new Project();
				project.setId(rs.getString("project.id"));
				project.setProid(rs.getString("project.proid"));
				project.setName(rs.getString("project.name"));
				//project.setVersion(rs.getString("project.version"));

				response.add(project);
			}while(rs.next());
		}
		conn.close();

		return response;
	}

	public boolean save(Project project) 
	throws SQLException, ClassNotFoundException{

		String sql="insert into project(proid,name,version) values(?,?,?)";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();

		ps.setString(1, project.getProid());
		ps.setString(2, project.getName());
		ps.setString(3,project.getVersion());

		result = ps.executeUpdate();
		conn.close();

		if(result>0){
			return true;
		}else{
			return false;
		}
	}

	public boolean update(Project project) 
	throws SQLException, ClassNotFoundException{

		String sql="update project set name=?,version=? where proid=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();

		ps.setString(1, project.getName());
		ps.setString(2, project.getVersion());
		ps.setString(3,project.getProid());

		result = ps.executeUpdate();
		conn.close();

		if(result>0){
			return true;
		}else{
			return false;
		}
	}
}
